import pandas as pd
import util.util as util

data_path = "/Users/yangfan/Desktop/excel/"

def lib_get_de_sum_data():
    file_name = data_path + "de_sum.xlsx"
    return pd.read_excel(file_name)

def lib_get_finance_data():
    file_name = data_path + "stock_finance.xlsx"
    return pd.read_excel(file_name, None)

def lib_get_pe_and_pb_data():
    file_name = data_path + "stock_pe_pb.xlsx"
    return pd.read_excel(file_name, index_col=0, sheet_name=None)

def lib_get_operation_data():
    file_name = data_path + "operation.xlsx"
    return pd.read_excel(file_name, None)

def lib_get_stocks_data():
    # 数据文件
    file_name = data_path + "stocks.xlsx"
    return pd.read_excel(file_name)

# 获取月数据
def lib_get_month_data():

    # 数据文件
    file_name = data_path + "month_level_point.xlsx"
    data = pd.read_excel(file_name, index_col=0,sheet_name=None)

    stocks = lib_get_all_stock()
    stocks = stocks.set_index('name')

    for k,v in data.items():
        stock = stocks.loc[k]
        v['trade_code'] = stock.trade_code
        v['stock'] = k
        v['wg'] = stock.wg
        v['tag'] = stock.tag
        v['wg_industry'] = stock.wg_industry
        v['category'] = stock.category
        v['next_month_pct'] = v['month_pct'].shift(-1)

    return data

# 获取周数据
def lib_get_week_data():
    # 数据文件
    file_name = data_path + "week_level_point.xlsx"
    
    data = pd.read_excel(file_name,index_col=0, sheet_name=None)
    
    stocks = lib_get_all_stock()
    stocks = stocks.set_index('name')

    for k,v in data.items():
        stock = stocks.loc[k]
        v['trade_code'] = stock.trade_code
        v['stock'] = k
        v['wg'] = stock.wg
        v['tag'] = stock.tag
        v['wg_industry'] = stock.wg_industry
        v['category'] = stock.category
        v['next_week_pct'] = v['week_pct'].shift(-1)

        #v['15disPct'] = round(v['15dis'] / v['15w_gb'] * 100, 2)
        #v['disPct'] = round(v['dis'] / v['60w_gb'] * 100, 2)


    return data


def lib_get_all_stock():
    shares = pd.read_csv('data/share_pool.txt')
    shares['category'] = '股票'

    etfs = pd.read_csv('data/etf_pool.txt')
    etfs['category'] = 'ETF'

    #shares = shares.append(etfs)
    shares = util.merge(shares, etfs)

    indexes = pd.read_csv('data/index_pool.txt')
    indexes['category'] = '指数'

    #shares = shares.append(indexes)
    shares = util.merge(shares, indexes)


    wugu = lib_get_wugu_data()
    wugu_prefix = wugu.add_prefix("wg_")
    shares = pd.merge(shares, wugu_prefix[['wg_industry']],left_on="name", right_on="stock", how="left")

    intro = lib_get_introduce_data()
    shares = pd.merge(shares, intro[['rank']],left_on="name", right_on="name", how="left")
    
    return shares


def lib_get_north_bound_data():
    file_name = data_path + "north_bound_ratio.xlsx"
    return pd.read_excel(file_name, sheet_name=None)

def lib_get_gb_data():
    file_name = data_path + "gb_sta.xlsx"
    return pd.read_excel(file_name,index_col=0, sheet_name=None)

def lib_get_wugu_data():
    file_name = data_path + "operation.xlsx"
    return pd.read_excel(file_name,index_col=0, sheet_name="吾股")

def lib_get_introduce_data():
    file_name = data_path + "operation.xlsx"
    return pd.read_excel(file_name,index_col=0, sheet_name="简介")

def lib_get_daily_gb_data():
    file_name = data_path + "daily_gb_sta.xlsx"
    return pd.read_excel(file_name,index_col=0, sheet_name=None)